package com.cn.wanxi.util;

import com.cn.wanxi.model.NavModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 手写JDBC
 */
public class Mysql {

    public static void main(String[] args) throws SQLException {
//        new Mysql().add();
//        new Mysql().update();
//        new JDBC().update("delete from nav where id=6");
//        JDBC.getJdbc().update("delete from nav where id=6");
//        new Mysql().query();

        new Mysql().aa();
    }

    private int aa() {
        try {
//            System.out.println("try");
//            int i=10/0;
            return returntry();
        } catch (Exception e) {
            return returncatch();
        } finally {
            System.out.println(11);
            return returnfinally();
        }
    }

    private int returntry() {
        System.out.println("returntry");
        return 1;
    }

    private int returncatch() {
        System.out.println("returncatch");
        return 2;
    }

    private int returnfinally() {
        System.out.println("returnfinally");
        return 3;
    }

    public void query() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://121.199.55.96:3306/canyin04?serverTimezone=UTC&characterEncoding=UTF-8";
        String user = "canyin04";
        String password = "123456";
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
            String sql = "select * from nav";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            List<NavModel> list = new ArrayList<>();
//          知道有多少条数据，则用for循环
//            for (int i = 0; i < 10; i++) {
//            resultSet.next()如果还有下一条，则返回true，如果没有下一条，则返回false
            while (resultSet.next()) {
                NavModel model = new NavModel();
                model.setHref(resultSet.getString("href"));
                model.setName(resultSet.getString("title"));
                list.add(model);
            }
            connection.close();
            preparedStatement.close();
            resultSet.close();
//            for (NavModel m:list
//                 ) {
//                System.out.println(m);
//            }


//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(connection);

    }

    private void update() throws SQLException {
//        1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
//        2.取得数据库链接
        String url = "jdbc:mysql://121.199.55.96:3306/canyin04?serviceTimezone=UTC&characterEncoding=UTF-8";
        String user = "canyin04";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);
//        3.sql语句
        String sql = "update nav set title='网站首页',href='/html/home.html',is_show=1 where id=7";
//        4.编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        5.执行sql语句
        int i = preparedStatement.executeUpdate();
        System.out.println(i);

    }

    private void add() throws SQLException {
        //        链接数据库
//        如果要链接别人的东西，我们必须要有他提供的接口，或者工具包，所以我们需要下载mysql的工具包，驱动
//       1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
//       2.连接数据：url，用户名，密码
//        jdbc:mysql://121.199.55.96:3306/canyin04
//        String url = "jdbc:mysql://121.199.55.96:3306/canyin04";
        String url = "jdbc:mysql:///canyin?serverTimezone=UTC&characterEncoding=UTF-8";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
//       已经得到链接，我们需要对数据进行新增
//        3.需要sql
        String sql = "INSERT INTO `cuisine` (`name`, `is_show`, `sort`,create_time) " +
                "VALUES ('10', '10', '10',date_format(now(),'%Y-%m-%d'));\n";
//        String sql="select * from company where id=1";
//        4.编译sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        对于mysql新增和修改和删除 都是更新
//        5.执行sql语句,并返回影响的条数
        int i = preparedStatement.executeUpdate();
//        preparedStatement.executeQuery();//查询
        System.out.println(i);

//        6.关闭链接
        connection.close();
        preparedStatement.close();
    }
}
